﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Zor_Modes;
using Zor_IDAL;
using Zor_Tools;
using System.Data;
using Dapper;

namespace Zor_DAL
{
   public  class Zor_NaturalPerson_DAL<T> where T : class
    {
        #region -自然人管理 -
        /// <summary>
        /// 获取自然人管理列表（不分页）
        /// create zhl by 2017.3.28     
        /// </summary>
        /// <returns></returns>
        public static List<T> SelectViewAcceptance(string where = null)
        {
            string strwhere = "";
            if (!string.IsNullOrEmpty(where))
            {
                strwhere = "where  1=1 and  " + where;
            }
            var sql = "select * from [dbo].[V_Acceptance] " + strwhere + "  order by Acceptance_Id ";
            return DapperQuery<T>.DoQueryList(sql, null);
        }
     
        /// <summary>
        /// 查询所有街道
        /// create zhl by 2017.3.28     
        /// </summary>
        /// <returns></returns>
       public static List<T> SelectStreet()
        {
            var sql = "select[Dt_Id],[Dt_Name] from [dbo].[Sys_Department] where [Dt_Level]=3 and [Dt_Name]like '%街道%'";
            return DapperQuery<T>.DoQueryList(sql, null);
        }
        /// <summary>
        /// 查询当前街道(根据当前部门ID)
        /// create zhl by 2017.3.29    
        /// </summary>      
        /// <param name="dt_Id">根据社区ID</param>
        /// <returns></returns>
       public static List<T> SelectStreetById(int dtid)
       {
           var sql = "select [Dt_Id],[Dt_Name]  from [dbo].[Sys_Department] where [Dt_Id]= " + dtid + " ";
           return DapperQuery<T>.DoQueryList(sql, null);
       }
       /// <summary>
       /// 查询所有街道(根据社区ID)
       /// create zhl by 2017.3.29    
       /// </summary>      
       /// <param name="dt_Id">根据社区ID</param>
      /// <returns></returns>
       public static T SelectStreet(int dt_Id)
       {
           var sql = "select [Dt_ParentId] from [dbo].[Sys_Department] where [Dt_Id]=" + dt_Id + " and [Dt_Level]=4 ";
           return DapperQuery<T>.DoQueryT(sql, new { Dt_Id = dt_Id });
       }

        /// <summary>
        /// 查询所有社区
        /// create zhl by 2017.3.28
        /// <param name="parentid">街道ID</param>
        /// </summary>
        /// <returns></returns>
       public static List<T> SelectCommunity(int parentid)
        {
            var sql = "select[Dt_Id],[Dt_Name] from [dbo].[Sys_Department] where Dt_ParentId=@Dt_ParentId";
            return DapperQuery<T>.DoQueryList(sql, new { Dt_ParentId = parentid });
        }


       /// <summary>
       /// 查询地址
       /// </summary>
       /// <param name="parentid"></param>
       /// <returns></returns>

       public static List<T> GetAcceptanceAddress(string NatPer_Name, string Tenant_Name)
       {
           var sql = "select Acceptance_Address from dbo.V_Acceptance where NatPer_Name ='" + NatPer_Name + "' and Tenant_Name ='" + Tenant_Name + "'";
           return DapperQuery<T>.DoQueryList(sql, "");
       }    

        /// <summary>
        /// 自然人管理-新增
        /// create zhl by 17-3-30
        /// </summary>
        /// <param name="naturalPerson">自然人实体</param>
        /// <param name="lease">租赁实体</param>
        /// <param name="acceptance">受理单实体</param>
        /// <param name="tenant">租户实体</param>
        /// <param name="house">房屋实体</param>
        /// <param name="unitInfo">单元实体</param>
        /// <param name="Build">楼宇实体</param>
        /// <returns> Succee = 1//成功 Failure = 2, //失败Error = 3 //异常</returns>
       public static BaseAnswer InsertNaturalPerson(Zor_NaturalPerson naturalPerson, Zor_Lease lease, Zor_Acceptance acceptance, Zor_Tenant tenant, Zor_Hous house, Zor_UnitInfor unitInfo, Zor_Building Build)
        {
           var sql = "select Dt_AmdCode from [dbo].[Sys_Department] where [Dt_Id]=@Dt_Id";
            Sys_Department Department = DapperQuery<Sys_Department>.DoQueryT(sql, new { Dt_Id = acceptance.Dt_Id });
            acceptance.Acceptance_TicketNumber = Department.Dt_AmdCode.ToString()+ DateTime.Now.ToString("yyyyMMddhhmmss");
          
            var param = new DynamicParameters();          
            param.Add("@NatPer_Name", naturalPerson.NatPer_Name);           
            param.Add("@NatPer_Phone", naturalPerson.NatPer_Phone);
            param.Add("@NatPer_IDNumber", naturalPerson.NatPer_IDNumber);
            param.Add("@User_Id", naturalPerson.NatPer_CreateName);
            param.Add("@CreateTime", naturalPerson.NatPer_CreateTime);
            param.Add("@Acceptance_TaxStartTime ", acceptance.Acceptance_TaxStartTime);
            param.Add("@Acceptance_TaxEndTime ", acceptance.Acceptance_TaxEndTime);
            param.Add("@Acceptance_TicketNumber ", acceptance.Acceptance_TicketNumber);          
            param.Add("@Acceptance_TaxNumber", acceptance.Acceptance_TaxNumber);
            param.Add("@Acceptance_PropertyTax ", acceptance.Acceptance_PropertyTax);
            param.Add("@Acceptance_PersonalTax ", acceptance.Acceptance_PersonalTax);
            param.Add("@Acceptance_Tax ", acceptance.Acceptance_Tax);
            param.Add("@Acceptance_TotalTax ", acceptance.Acceptance_TotalTax);       
            param.Add("@Acceptance_Address", acceptance.Acceptance_Address);
            param.Add("Dt_Id ", acceptance.Dt_Id);
            param.Add("@Lease_StartTime  ", lease.Lease_StartTime);
            param.Add("@Lease_EndTime  ", lease.Lease_EndTime);
            param.Add("@Lease_Rent  ", lease.Lease_Rent);
            param.Add("@Tenant_Type", tenant.Tenant_Type);
            param.Add("@Tenant_Name ", tenant.Tenant_Name);
            param.Add("@Hs_Number ", house.Hs_Number);
            param.Add("@Hs_household ", house.Hs_household);
            param.Add("@Hs_Cqsx ", house.Hs_Cqsx);
            param.Add("@Hs_attribute ", house.Hs_attribute);
            param.Add("@Hs_Proportion ", house.Hs_Proportion);
            param.Add("@Bd_Name ", Build.Bd_Name);
            param.Add("@Uim_Name ", unitInfo.Uim_Name);
            param.Add("@narid", 0, DbType.Int32, ParameterDirection.ReturnValue);
            param.Add("@Hs_Flage ", house.Hs_Flage);
            using (IDbConnection conn = DapperConn.OpenSqlConnection())
            {
                var Answer = conn.Execute("Pro_NaturalPerson", param, null, null, CommandType.StoredProcedure);
                return param.Get<int>("@narid") > 0 ? new BaseAnswer { AnswerType = BaseAnswer.Answers.Succee } : new BaseAnswer { AnswerType = BaseAnswer.Answers.Failure };
            }
          
        }

       /// <summary>
       /// 自然人管理-修改
       /// create zhl by 17-3-30
       /// </summary>
       /// <param name="naturalPerson">自然人实体</param>
       /// <param name="lease">租赁实体</param>
       /// <param name="acceptance">受理单实体</param>
       /// <param name="tenant">租户实体</param>
       /// <param name="house">房屋实体</param>
       /// <param name="unitInfo">单元实体</param>
       /// <param name="Build">楼宇实体</param>
       /// <returns> Succee = 1//成功 Failure = 2, //失败Error = 3 //异常</returns>
       public static BaseAnswer UpdateNaturalPerson(Zor_NaturalPerson naturalPerson, Zor_Lease lease, Zor_Acceptance acceptance, Zor_Tenant tenant, Zor_Hous house, Zor_UnitInfor unitInfo, Zor_Building Build)
       {
           List<string> lstsql = new List<string>();
          
               //更新自然人表
               lstsql.Add(string.Format("update [Zor_NaturalPerson] set NatPer_Name='{0}',NatPer_Phone='{1}',NatPer_IDNumber='{2}',NatPer_ModifyName='{3}',NatPer_ModifyDate='{4}' where NatPer_Id=" + naturalPerson.NatPer_Id + "", SqlFilterHelper.FiltText(naturalPerson.NatPer_Name), SqlFilterHelper.FiltText(naturalPerson.NatPer_Phone), SqlFilterHelper.FiltText(naturalPerson.NatPer_IDNumber), naturalPerson.NatPer_ModifyName, naturalPerson.NatPer_ModifyDate));
               //更新租户信息表
               lstsql.Add(string.Format("update [Zor_Tenant] set Tenant_Type='{0}',Tenant_Name='{1}' where Tenant_Id=" + tenant.Tenant_Id + "", tenant.Tenant_Type, SqlFilterHelper.FiltText(tenant.Tenant_Name)));
               //更新租赁关系表
               lstsql.Add(string.Format("update [Zor_Lease] set Lease_StartTime='{0}',Lease_EndTime='{1}',Lease_Rent='{2}' where Lease_Id=" + lease.Lease_Id + "", lease.Lease_StartTime, lease.Lease_EndTime, lease.Lease_Rent));
               //更新受理单表
               lstsql.Add(string.Format("update [Zor_Acceptance] set Acceptance_TaxStartTime='{0}',Acceptance_TaxEndTime='{1}',Acceptance_TaxNumber='{2}',Dt_Id='{3}',Acceptance_Address='{4}',Acceptance_PropertyTax='{5}',Acceptance_PersonalTax='{6}',Acceptance_Tax='{7}',Acceptance_TotalTax='{8}' where Acceptance_Id=" + acceptance.Acceptance_Id + "", acceptance.Acceptance_TaxStartTime, acceptance.Acceptance_TaxEndTime, acceptance.Acceptance_TaxNumber, acceptance.Dt_Id, SqlFilterHelper.FiltText(acceptance.Acceptance_Address), acceptance.Acceptance_PropertyTax, acceptance.Acceptance_PersonalTax, acceptance.Acceptance_Tax, acceptance.Acceptance_TotalTax));
               //更新楼宇表
               lstsql.Add(string.Format("update [Zor_Building] set Bd_Name='{0}' where Bd_Id =" + Build.Bd_Id + "", Build.Bd_Name));
               //更新单元表
               lstsql.Add(string.Format("update [Zor_UnitInfor] set Uim_Name='{0}' where Uim_Id =" + unitInfo.Uim_Id + "", unitInfo.Uim_Name));
               //更新房屋信息表
               lstsql.Add(string.Format("update [Zor_Hous] set Hs_Number='{0}',Hs_Cqsx='{1}',Hs_attribute='{2}',Hs_Proportion='{3}',Hs_Flage='{4}' where Hs_Id=" + house.Hs_Id + "", house.Hs_Number, house.Hs_Cqsx, house.Hs_attribute, house.Hs_Proportion, house.Hs_Flage));
          
           BaseAnswer bar = DapperTransaction.DoTransaction(lstsql);
           return bar;
       }

        /// <summary>
        /// 删除自然人
        /// create zhl by 17-3-19
        /// </summary>
        /// <param name="id">受理单id</param>
        /// <returns> Succee = 1//成功 Failure = 2, //失败Error = 3 //异常</returns>
       public static BaseAnswer DeleteNaturalPerson(int Acceptance_Id, int Lease_Id, int Hs_Id, int Uim_Id, int Bd_Id, int Hs_attributes)
        {
            int Dtid = RequestSession.GetSessionUser().DT_Id; //获取当前登录人的部门ID
            int StreeId = RequestSession.GetSessionUser().Dt_ParentId;//获取当前登录的部门父ID
            var sql = "select Acceptance_Id from [Zor_Review] where Acceptance_Id in(" + Acceptance_Id + ") and Review_State !=3";//根据受理单查找评议表是否存在当前受理单
            List<dynamic> sqldelete = DapperQuery<dynamic>.DoQueryList(sql, null);
            if (sqldelete.Count != 0)
            {
                return new BaseAnswer { AnswerType = BaseAnswer.Answers.Error, AnswerMeg = "当前受理单已提交至评议，不能删除！" };
            }
            else
            {
                List<string> lstsql = new List<string>();
                //1.删除受理单表
                lstsql.Add(string.Format("Delete from [Zor_Acceptance] where Acceptance_Id = " + Acceptance_Id + ""));                
                //2.删除租赁关系表
                lstsql.Add(string.Format("Delete from [Zor_Lease] where Lease_Id = " + Lease_Id + " "));
                //3.删除房屋表
                lstsql.Add(string.Format("Delete from [Zor_Hous] where Hs_Id = " + Hs_Id + " "));
                //4.修改房屋统计表中的（1单元2楼宇3社区4街道）
                lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Uim_Id = " + Uim_Id + " and Type= 1 ")); //单元
                lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Bd_Id = " + Bd_Id + " and Type= 2 "));//楼宇
                if (Hs_attributes == 2) //修改社区、街道办-非住宅
                {
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Dt_Id = " + Dtid + " and Type= 3 and  HousType =1"));//社区
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Stree_ID = (select Dt_ParentId from  dbo.Sys_Department where Dt_Id=" + Dtid + ") and Type= 4 and  HousType =1"));//街道 
                }
                else//修改社区、街道办-住宅
                {
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Dt_Id = " + Dtid + " and Type= 3 and  HousType =2"));//社区
                    lstsql.Add(string.Format("update [Zor_HouseCount] set Number=Number - 1 where Stree_ID = (select Dt_ParentId from  dbo.Sys_Department where Dt_Id=" + Dtid + ") and Type= 4 and  HousType =2"));//街道      
                }
                
                BaseAnswer bar = DapperTransaction.DoTransaction(lstsql);
                return bar;          
            }
            
        }

        /// <summary>
        /// 推送
        /// create zhl by 17.3.30
        /// </summary>
        /// <param name="id">受理单id</param>
        /// <returns>2--当前受理单已推送-1操作失败1--操作成功的标识</returns> 
        public static int Push(int id, int acceptancestate, int dtid)
        {
            var param = new DynamicParameters();
            param.Add("@Acceptance_Id", id);
            param.Add("@Review_TotalTax", 0);
            param.Add("@Review_Rent", 0);
            param.Add("@Review_PersonalTax", 0);
            param.Add("@Review_PropertyTax", 0);
            param.Add("@Review_Tax", 0);
            param.Add("@Review_Comment", "");
            param.Add("@Acceptance_State", acceptancestate);
            param.Add("@User_Id", RequestSession.GetSessionUser().User_Id);
            param.Add("Dt_Id", dtid);
            param.Add("@Review_CreateTime", TimeHelper.ToTimeStamp(DateTime.Now));          
            param.Add("@narid", 0, DbType.Int32, ParameterDirection.ReturnValue);
            using (IDbConnection conn = DapperConn.OpenSqlConnection())
            {
                var Answer = conn.Execute("Pro_AcceptanceReview", param, null, null, CommandType.StoredProcedure);
                return param.Get<int>("@narid");
            }          
        }

         /// <summary>
        /// 社区受理单推送
        /// create zhl by 17.3.30
        /// </summary>
        /// <param name="id">受理单id</param>
        /// <returns>2--当前受理单已推送-1操作失败1--操作成功的标识</returns> 
        public static int AccPush(int acceptanceid, int acceptancestate)
        {
            var param = new DynamicParameters();
            param.Add("@Acceptance_Id", acceptanceid);
            param.Add("@Review_TotalTax", 0);
            param.Add("@Review_Rent", 0);
            param.Add("@Review_PersonalTax", 0);
            param.Add("@Review_PropertyTax", 0);
            param.Add("@Review_Tax", 0);  //评议后税金
            param.Add("@Review_Comment", "");
            param.Add("@Acceptance_State", acceptancestate);
            param.Add("@User_Id", RequestSession.GetSessionUser().User_Id);
            param.Add("Dt_Id", RequestSession.GetSessionUser().DT_Id);
            param.Add("@Review_CreateTime", TimeHelper.ToTimeStamp(DateTime.Now));
            param.Add("@narid", 0, DbType.Int32, ParameterDirection.ReturnValue);
            using (IDbConnection conn = DapperConn.OpenSqlConnection())
            {
                var Answer = conn.Execute("Pro_AcceptanceReview", param, null, null, CommandType.StoredProcedure);
                return param.Get<int>("@narid");
            }          
              
        }
        /// <summary>
        /// 推送并评议
        /// create zhl by 17.3.30
        /// </summary>
        /// <param name="ApeId">受理单id</param>
        /// <param name="propertytax">评议房屋税金</param>
        /// <param name="tax">评议税金</param>
        /// <param name="totaltax">评议计税总额</param>
        /// <param name="personaltax">评议个税</param>
        /// <param name="rentMoney">评议租金</param>
        /// <param name="comment"></param>
        /// <returns>2--当前受理单已推送-1操作失败1--操作成功的标识</returns>


        public static int PushReview(int ApeId, int propertytax, int tax, int totaltax, int personaltax, int rentMoney, string comment, int acceptancestate, int dtid)
        {
            var param = new DynamicParameters();
            param.Add("@Acceptance_Id", ApeId);
            param.Add("@Review_TotalTax", totaltax);
            param.Add("@Review_Rent", rentMoney);
            param.Add("@Review_PersonalTax", personaltax);
            param.Add("@Review_PropertyTax", propertytax);
            param.Add("@Review_Tax", tax);
            param.Add("@Review_Comment", comment);
            param.Add("@Acceptance_State", acceptancestate);
            param.Add("@User_Id", RequestSession.GetSessionUser().User_Id);
            param.Add("Dt_Id", dtid);
            param.Add("@Review_CreateTime", TimeHelper.ToTimeStamp(DateTime.Now));
            param.Add("@narid", 0, DbType.Int32, ParameterDirection.ReturnValue);
            using (IDbConnection conn = DapperConn.OpenSqlConnection())
            {
                var Answer = conn.Execute("Pro_AcceptanceReview", param, null, null, CommandType.StoredProcedure);
                return param.Get<int>("@narid");
            }
        }
        #endregion
    }
}
